Functions Are Data Too (Defunctionalization for PL/SQL)

We demonstrate a full-fledged implementation of first-class functions for the widely used PL/SQL database programming language. Functions are treated as regular data items that may be (1) constructed at query runtime, (2) stored in and retrieved from tables, (3) assigned to variables, and (4) passed to and from other (higher-order) functions. The resulting PL/SQL dialect concisely and elegantly expresses a wide range of new query idioms which would be cumbersome to formulate if functions remained second-class citizens. We include a diverse set of application scenarios that make these advantages tangible. First-class PL/SQL functions require featherweight syntactic extensions only and come with a non-invasive implementation—the defunctionalization transformation—that can entirely be built on top of existing relational DBMS infrastructure. An interactive demonstrator helps users to experiment with the “function as data” paradigm and to earn a solid intuition of its inner workings. 1. FUNCTIONS ARE DATA TOO PL/SQL programming [2] marks one of the predominant approaches to implement application logic close to relational data: regular SQL queries may be embedded in programs that feature—among other elements typically found in scripting languages—statement sequences, control flow and exception handling constructs, or variable assignment. Since the PL/SQL interpreter or compiler tightly integrates with the database engine, such programs can manipulate persistent data efficiently without crossing database kernel boundaries. The colloquial term “stored procedures” is widely used as a stand-in for the PL/SQL approach as a whole and functions (or procedures) indeed are its primary unit of program organization. Yet, functions remain second-class citizens in the language: functions exclusively assume the role of code units, defined and named at compile time, ready for subsequent invocation. Permission to make digital or hard copies of all or part of this work for personal or classroom use is granted without fee provided that copies are not made or distributed for profit or commercial advantage and that copies bear this notice and the full citation on the first page. To copy otherwise, to republish, to post on servers or to redistribute to lists, requires prior specific permission and/or a fee. Articles from this volume were invited to present their results at The 39th International Conference on Very Large Data Bases, August 26th 30th 2013, Riva del Garda, Trento, Italy. Proceedings of the VLDB Endowment, Vol. 6, No. 12 Copyright 2013 VLDB Endowment 2150-8097/13/10... $ 10.00. In this demonstration (and a companion paper that zooms in on the conceptual details, implementation, and performance [4]), we explore a dialect of PL/SQL in which functions assume the role of data instead. As such, functions may be defined at query runtime, assigned to variables, passed to and from other functions, and stored in data structures (tables, notably). Functions as first-class citizens enable a functional style of PL/SQL programming that (1) nicely complements existing practice but also (2) paves the way for new, particularly concise and elegant query idioms. The “functions as data” idea is effective in the sense that it brings far-reaching query formulation opportunities while few language extensions suffice to anchor the paradigm in PL/SQL: • FUNCTION(t1) RETURNS t2, the type of functions from t1 to t2, is now a data type (just like INTEGER or VARCHAR(n)), • function names—built-in (like atan or upper) and userdefined—denote regular values (alas of function type), • FUNCTION(x t1) RETURNS t2 AS BEGIN e END denotes a literal function with argument x and body e (just like 42 denotes a literal of type INTEGER), and finally • e(e1,. . . ,en) is regarded as a valid (dynamic) function call if expression e evaluates to a value of function type. Further, first-class functions come with a lightweight and efficient implementation approach, defunctionalization [8], that does not require database kernel changes [4]. Cast as a source-to-source transformation, defunctionalization can be non-invasively applied to any PL/SQL host. The present demonstration builds on a PL/SQL preprocessor that sits entirely on top of PostgreSQL 9 [1]. 2. PL/SQL WITH A FUNCTIONAL MINDSET Despite being a modest language extension with a noninvasive implementation, first-class functions have a profound impact on how ideas can be expressed in terms of PL/SQL queries. We have collected three sample application scenarios here, ordered from customary to offbeat, and include many more in the actual software demonstration. In the PL/SQL listings below, we have placed a mark ( ) in the gutter where the language extensions come into play. A Functions That Travel With Data. In a TPC-H benchmark database, the status of an order is determined by the status of its line items [9, § 4.2.3]: if all line items either agree on status ’F’ or ’O’ (finalized vs. open) then this also Of course, the idea does not hinge on this particular syntax.